package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.Constants;
import com.hellogood.yaohuo.shared.entity.YhDetail;
import com.hellogood.yaohuo.shared.entity.YhForm;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class YhFormDAO extends DBHelper {

	Logger log = Logger.getLogger(this.getClass());
	String hxDbName = ConnectionPool.hxDbName;

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<YhForm> yhFormList = new ArrayList<YhForm>();
		if (rs != null) {
			while (rs.next()) {
				YhForm yhForm = new YhForm();
				yhForm.setYhBillNo(rs.getString("YhBillNo"));
				yhForm.setYhDate(rs.getString("YhDate"));
				yhForm.setOrgCode(rs.getString("OrgCode"));
				yhForm.setOrgName(rs.getString("OrgName"));
				yhForm.setCounts(rs.getInt("Counts"));
				yhForm.setCost(rs.getBigDecimal("Cost"));
				yhForm.setNetCost(rs.getBigDecimal("NetCost"));
				yhForm.setTotal(rs.getBigDecimal("Total"));
				yhForm.setStaffCode(rs.getString("StaffCode"));
				yhForm.setStaffName(rs.getString("StaffName"));
				yhForm.setTimeMark(rs.getString("CreateTime"));
				yhForm.setRzDate(rs.getString("RzDate"));
				yhForm.setRemark(rs.getString("Remark"));
				yhForm.setStatus(rs.getString("Status"));
				yhFormList.add(yhForm);
			}
		}
		return yhFormList;
	}

	public String getLastYhBillNo(String billNo) {
		String sql = "SELECT top 1 yhBillNo FROM YhForm WHERE yhBillNo like '" + billNo + "%' ORDER BY Yhbillno DESC";
		// Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = DBConnection.getInstance().getConnection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				billNo = rs.getString("yhBillNo");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				// ConnectionPool.getInstance().release(conn);
			}
		}
		return billNo;
	}

	public String createYaohuo(YhForm form, boolean isModify) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		if (isModify) {
			String sql = "DELETE FROM yhForm WHERE YhBillNo = '" + form.getYhBillNo() + "'";
			sqls.add(sql);
			sql = "DELETE FROM yhDetail WHERE YhBillNo = '" + form.getYhBillNo() + "'";
			sqls.add(sql);
		}

		String sql = "INSERT INTO YhForm(yhBillNo,yhDate,orgCode,counts,cost,netCost,total,staffCode,staffName,rzDate,remark,createTime) VALUES('" + form.getYhBillNo()
				+ "',CONVERT(varchar(100), GETDATE(), 23),'" + form.getOrgCode() + "'," + form.getCounts() + "," + form.getCost() + "," + form.getNetCost() + "," + form.getTotal() + ",'"
				+ form.getStaffCode() + "','" + form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "','" + form.getTimeMark() + "')";
		sqls.add(sql);

		List<YhDetail> list = form.getList();
		if (list != null && (!list.isEmpty())) {
			for (YhDetail d : list) {
				sql = "INSERT INTO YhDetail(yhBillNo,pluCode,pluName,barCode,depCode,bra,color,csize,jPrice,netJPrice,price,counts,cost," + "netCost,total,rzDate,remark,orgCode,createTime) VALUES('"
						+ d.getYhBillNo() + "','" + d.getPluCode() + "','" + d.getPluName() + "','" + d.getBarCode() + "','" + d.getDepCode() + "','" + d.getBra() + "','" + d.getColor() + "','"
						+ d.getSize() + "'," + d.getjPrice() + "," + d.getjPrice() + "," + d.getPrice() + "," + d.getCounts() + "," + d.getCost() + "," + d.getCost() + "," + d.getTotal() + ",'"
						+ d.getRzDate() + "','" + d.getRemark() + "','" + d.getOrgCode() + "',getDate())";
				sqls.add(sql);
			}
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	@SuppressWarnings("unchecked")
	public List<YhForm> searchYhFormByQuyu(String quyuCode, String startDate, String endDate, String rz) {
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}
		String sql = "SELECT f.*,(SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName FROM YhForm f WHERE f.OrgCode IN (" + orgs
				+ ") AND cast(f.YhDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate + "'),120) and cast(f.YhDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ endDate + "'),120)";
		if (rz.equals(Constants.RUZHANG_CODE_YES)) {
			sql = sql + " and rzDate <> ''";
		} else if (rz.equals(Constants.RUZHANG_NAME_NO)) {
			sql = sql + " and rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<YhForm>) list;
	}

	@SuppressWarnings("unchecked")
	public List<YhForm> searchYhForm(String orgCode, String startDate, String endDate, String rz) {
		String sql = "SELECT f.*,(SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName  FROM YhForm f WHERE f.OrgCode = '" + orgCode
				+ "' AND cast(f.YhDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate + "'),120) and cast(f.YhDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ endDate + "'),120)";
		if (rz.equals("已入账")) {
			sql = sql + " and rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " and rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<YhForm>) list;
	}

	public YhForm getFormByBillNo(String billNo) {
		String sql = "SELECT f.*,(SELECT OrgName FROM " + hxDbName + "..SubShop WHERE OrgCode = f.OrgCode) AS OrgName FROM yhForm f WHERE f.yhBillNo = '" + billNo + "'";
		List<?> list = super.query(sql);
		if (list != null && (!list.isEmpty())) {
			return (YhForm) list.get(0);
		}
		return null;
	}

	public String deleteYaohuo(String billNo) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		String sql = "DELETE FROM yhForm WHERE yhBillNo = '" + billNo + "'";
		sqls.add(sql);
		sql = "DELETE FROM yhDetail WHERE yhBillNo = '" + billNo + "'";
		sqls.add(sql);
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}

	public String rz(String bill) {
		String result = "";
		String sql = "UPDATE yhForm SET rzDate = CONVERT(VARCHAR(10),getDate(),120) WHERE yhBillNo = '" + bill + "'";
		try {
			super.execute(sql);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}
}
